package util;

import pojo.Teacher;

import java.sql.Statement;

public class TeacherDBUtil extends DBUtil {

    public int deleteTeaByTno(int tno)  {
        int i=0;
        try{
            getConn();
            String sql = "delete from teacher where tno='" + tno + "'";
            Statement st = conn.createStatement();
            i = st.executeUpdate(sql);
            if (i > 0) {
                System.out.println("教师删除成功");
            } else {
                System.out.println("教师删除失败");
            }
        }catch (Exception e){
            e.printStackTrace();
        }
       finally {
            closeConn();
        }
        return i;
    }

    public int addTeacher(Teacher tea) {
        int i = 0;
        try {
            getConn();
            String sql = "insert into teacher values('" + tea.getTname() + "','" + tea.getSex() + "'," + tea.getTel() + "," + tea.getGno() + "," + tea.getTno() + ")";
            //创建 statement
            Statement st = conn.createStatement();
            //执行sql的 查询，得到结果。结果封装在 rs 中
            i = st.executeUpdate(sql);
            if (i > 0) {
                System.out.println("教师添加成功");
            } else {
                System.out.println("教师添加失败");
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeConn();
        }
        return i;
    }
//根据班级编号查询教师信息
    public Teacher getTeacherByGno(Integer gno) {
        Teacher tea = null;
        try {
            getConn();
            String sql = "select *  from teacher where gno="+gno;
            st = conn.createStatement();
            rs = st.executeQuery(sql);
            if (rs.next()) {
                tea = new Teacher(
                        rs.getInt(5),
                        rs.getString(1),
                        rs.getString(2),
                        rs.getLong(3),
                        rs.getInt(4));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeConn();
        }
        return tea;
    }

    public int updateTea(Teacher tea) throws Exception {
        int i = 0;
        try {
            getConn();
            st = conn.createStatement();
            String sql = getTeaSql(tea);
            i = st.executeUpdate(sql);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeConn();
        }
        return i;
    }
    //生成动态sql
    public String getTeaSql(Teacher tea){
        StringBuffer sb=new StringBuffer("update teacher set");
        if(tea.getTname()!=null){
            sb.append(" tname=");
            sb.append("'");
            sb.append(tea.getTname());
            sb.append("',");
        }
        if(tea.getSex()!=null){
            sb.append(" sex=");
            sb.append("'");
            sb.append(tea.getSex());
            sb.append("',");
        }
        if(tea.getTel()!=null){
            sb.append(" tel=");
            sb.append("'");
            sb.append(tea.getTel());
            sb.append("',");
        }
        if(tea.getGno()!=null){
            sb.append(" gno=");
            sb.append("'");
            sb.append(tea.getGno());
            sb.append("',");
        }

        sb.append(" where tno=");
        sb.append(tea.getTno());
        //获取最后一个,的下标
        int index=sb.lastIndexOf(",");
        sb.replace(index, index+1, "");
        return sb.toString();
    }
}
